﻿-- =============================================
-- Author:		Thanh
-- Create date: 23/5/2014
-- Sửa ngày: 23/7/2014
-- Description:	Kiểm tra số liệu
-- =============================================
ALTER PROCEDURE [dbo].[sp_T_KiemTraSoLieu]
	@maKho int
AS
BEGIN
	select t.ID_MatHang, vt.VatTu, t.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	,sum(w.NhapKH) as pNhapKH, sum(w.NhapTH) as pNhapTH, sum(w.XuatKH) as pXuatKH, sum(w.XuatTH) as pXuatTH
	from
		(
			select ct.ID_MatHang, ct.SLKH as NhapKH, 0 as XuatKH, ct.SLTH as NhapTH, 0 as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='N'
			union all
			select ct.ID_MatHang, 0 as NhapKH, ct.SLKH as XuatKH, 0 as NhapTH, ct.SLTH as XuatTH
			from tblSLNX ct, tblDPNX p
			where ct.ID_DPNX= p.ID_DPNX and p.NX='X'
		) as w, tblTon as t, tblVatTu as vt
	where w.ID_MatHang = t.ID_MatHang and t.ID_VatTu= vt.ID_VatTu and t.ID_Kho = @maKho
	group by  t.ID_MatHang, vt.VatTu, t.MaKyHieu, t.NamSX, t.Gia, t.SLSS11
	,t.SLNhapKH, t.SLNhapTH, t.SLXuatKH, t.SLXuatTH, t.SLTonKH, t.SLTonTH
	having (sum(w.NhapKH)<> t.SLNhapKH) or (sum(w.NhapTH)<> t.SLNhapTH)
	or (sum(w.XuatKH)<> t.SLXuatKH) or (sum(w.XuatTH)<> t.SLXuatTH)
	or ((t.SLSS11+sum(w.NhapKH)-sum(w.XuatKH)) <> t.SLTonKH)
	or ((t.SLSS11+sum(w.NhapTH)-sum(w.XuatTH)) <> t.SLTonTH)
END